using Firestarter.DB.Schema.BO;
using FireStarterModeller.BO;
using Firestarter.DB.Schema.Logic;
using FireStarterModeller.Test;
using Habanero.Testability;
using NUnit.Framework;

namespace Firestarter.DB.Schema.Test.Logic
{
    [TestFixture]
    public class TestDatabaseScriptCreator : TestBase
    {
        protected virtual DatabaseScriptCreator CreateDatabaseScriptCreator()
        {
            return new DatabaseScriptCreator();
        }

        private static DBTableBuilder CreateNewDBTableBuilder()
        {
            return CreateNewDBTableBuilder(GetRandomString());
        }

        private static DBTableBuilder CreateNewDBTableBuilder(string tableName)
        {
            return new DBTableBuilder(new DBTable(tableName));
        }

        private static string GetRandomString()
        {
            return RandomValueGen.GetRandomString();
        }
        //CREATE TABLE track(
        //  album CHAR(10),
        //  dsk INTEGER,
        //  posn INTEGER,
        //  song VARCHAR(255),
        //  FOREIGN KEY 'FKName' (album) REFERENCES album(id)
        //);
        //
        //CREATE TABLE album(
        //  id CHAR(10) NOT NULL PRIMARY KEY,
        //  title VARCHAR(100),
        //  artist VARCHAR(100)
        //);
        //UNIQUE `AK1`(`ggggg`, `hhhhhh`)
        //
        //CONSTRAINT `FKNAME` FOREIGN KEY 'FKName' (foreignKeyColumnList) REFERENCES referencedTable(referencedTableColumnList)

        #region Utils

        protected static DBTable CreateTableInfoWithSimpleAlternateKey
            (string tableName, string alternateKeyName, string columnName)
        {
            DBTable dbTable = new DBTable(tableName) { PrimaryKeyDB = new DBPrimaryKey()};
            DBColumn akDBColumn = new DBColumn(columnName) {DataType = "CHAR", DataLength = 38};
            dbTable.Columns.Add(akDBColumn);

            DBKey alternateDBKey = new DBKey {KeyName = alternateKeyName};
            alternateDBKey.KeyColumns.Add(new DBKeyColumn {Column = akDBColumn});
            dbTable.Keys.Add(alternateDBKey);
            return dbTable;
        }

        private static DBTable CreateDBTableWithForeignKey(out DBTable referencedDBTable)
        {
            DBTableBuilder referencedDBTableBuilder = CreateNewDBTableBuilder("ReferencedTable");
            referencedDBTable = referencedDBTableBuilder.Table;
            referencedDBTableBuilder.AddPrimaryKeyColumn();

            DBTableBuilder dbTableBuilder = CreateNewDBTableBuilder("MyTable");
            dbTableBuilder.AddPrimaryKeyColumn();
            dbTableBuilder.AddForeignKey("MyForeignKey", referencedDBTable);

            return dbTableBuilder.Table;
        }

        private static string expectedCreateTableBegin(string tableName)
        {
            return string.Format("CREATE TABLE `{0}` (", tableName);
        }
        #endregion

        [Test]
        public void Test_CreateTable_WithColumnsAndPrimaryKey()
        {
            //---------------Set up test pack-------------------
            DatabaseScriptCreator databaseScriptCreator = CreateDatabaseScriptCreator();
            const string idcolumnName = "IDColumn";
            const string idcolumnType = "CHAR";
            const int idColumnDataLength = 38;
            const string columnName = "MyColumn";
            const string columnDataType = "VARCHAR";
            const int columnDataLength = 50;

            DBTableBuilder dbTableBuilder = CreateNewDBTableBuilder();
            dbTableBuilder.AddPrimaryKeyColumn(idcolumnName, idcolumnType, idColumnDataLength);
            dbTableBuilder.AddColumn(columnName, columnDataType, columnDataLength);
            DBTable dbTable = dbTableBuilder.Table;
            //-------------Assert Preconditions -------------

            //---------------Execute Test ----------------------
            string sql = databaseScriptCreator.CreateTable(dbTable, new CreateTableSqlBuilder_MySql(dbTable.TableName));
            //---------------Test Result -----------------------
            string expected = string.Format
                ("CREATE TABLE `{0}` (`{1}` {2}({3}) NOT NULL, `{4}` {5}({6}) NULL, PRIMARY KEY(`{1}`))", 
                dbTable.TableName, 
                idcolumnName, idcolumnType, idColumnDataLength, 
                columnName, columnDataType, columnDataLength);
            Assert.AreEqual(expected, sql);

            //                string regex = RegexBuilderSQL.Create().StartText.WhiteSpace.ZeroToMany
            //                    .BeginCreateTable(dbTable.TableNameDB)
            //                    .WhiteSpace.ZeroToMany
            //                    .Open
            //                        .ColumnDefinition().WhiteSpace.ZeroToMany
            //                        .Specific(",").WhiteSpace.ZeroToMany
            //                    .Close.OneToMany
            //                    .Specific("PRIMARY KEY").WhiteSpace.ZeroToMany.Specific("(")
            //                    .Delimited(
            //
            //                        RegexBuilderSQL.Create().WhiteSpace.ZeroToMany.AlphaNumeric.OneToMany.WhiteSpace.ZeroToMany, ",", 1, 4)
            //
            //                    .Specific(")")
            //                    .EndCreateTable().EndText.ToString();
            //                StringAssert.IsMatch(regex, sql);
        }

        [Test]
        public void Test_CreateTable_WithColumns_NoPrimaryKey()
        {
            //---------------Set up test pack-------------------
            DatabaseScriptCreator databaseScriptCreator = CreateDatabaseScriptCreator();

            const string columnName = "MyColumn";
            const string columnDataType = "VARCHAR";
            const int columnDataLength = 50;
            const string column2Name = "MyColumn2";
            const string column2DataType = "CHAR";
            const int column2DataLength = 45;

            DBTableBuilder dbTableBuilder = CreateNewDBTableBuilder();
            dbTableBuilder.AddColumn(columnName, columnDataType, columnDataLength);
            dbTableBuilder.AddColumn(column2Name, column2DataType, column2DataLength);
            DBTable dbTable = dbTableBuilder.Table;
            //-------------Assert Preconditions -------------

            //---------------Execute Test ----------------------
            string sql = databaseScriptCreator.CreateTable(dbTable, new CreateTableSqlBuilder_MySql(dbTable.TableName));
            //---------------Test Result -----------------------
            string expected = string.Format
                ("CREATE TABLE `{0}` (`{1}` {2}({3}) NULL, `{4}` {5}({6}) NULL)", dbTable.TableName, 
                columnName, columnDataType, columnDataLength,
                column2Name, column2DataType, column2DataLength);
            Assert.AreEqual(expected, sql);
        }

        [Test]
        public void Test_CreateTable_WithColumns_AlternateKey_NoPrimaryKey()
        {
            //---------------Set up test pack-------------------
            DatabaseScriptCreator databaseScriptCreator = CreateDatabaseScriptCreator();
            const string alternateKeyString = "AK1";
            const string columnName = "Some Column";
            const string tableName = "testTableName";
            DBTable dbTable = CreateTableInfoWithSimpleAlternateKey(tableName, alternateKeyString, columnName);
            dbTable.PrimaryKeyDB = null;
            //-------------Assert Preconditions -------------

            //---------------Execute Test ----------------------
            string sql = databaseScriptCreator.CreateTable(dbTable, new CreateTableSqlBuilder_MySql(dbTable.TableName));
            //---------------Test Result -----------------------
            string expectedCreateTable = string.Format("CREATE TABLE `{0}` (", dbTable.TableName);
            string expectedAlternateKey = string.Format("CONSTRAINT `{0}` UNIQUE (`{1}`)", alternateKeyString, columnName);
            Assert.IsTrue
                (sql.EndsWith(", " + expectedAlternateKey + ")"),
                 sql + " should end with " + ", " + expectedAlternateKey + ")");
            Assert.IsTrue(sql.StartsWith(expectedCreateTable), sql + " should start with " + expectedCreateTable);
            Assert.IsFalse(sql.Contains("PRIMARY KEY"), "Should not contain a primary key");
        }

        [Test]
        public void Test_CreateTable_NoPrimaryKey()
        {
            //---------------Set up test pack-------------------
            DatabaseScriptCreator databaseScriptCreator = CreateDatabaseScriptCreator();
            DBTable dbTable = new DBTable(GetRandomString()) { PrimaryKeyDB = new DBPrimaryKey() };
            DBColumn dbColumn = new DBColumn("IDColumn") { DataType = "CHAR", DataLength = 38 };
            dbTable.Columns.Add(dbColumn);
            //-------------Assert Preconditions -------------

            //---------------Execute Test ----------------------
            string sql = databaseScriptCreator.CreateTable(dbTable, new CreateTableSqlBuilder_MySql(dbTable.TableName));
            //---------------Test Result -----------------------
            string expected = string.Format ("CREATE TABLE `{0}` (`{1}` {2}({3}) NULL)", 
                dbTable.TableName, dbColumn.ColumnName, dbColumn.DataType, dbColumn.DataLength);
            Assert.AreEqual(expected, sql);
        }

        // ReSharper restore AccessToStaticMemberViaDerivedType
/*
        [Test]
        [Ignore("This needs to be implemented inside the builder")]
        //TODO Mark 11 May 2009: Ignored Test - This needs to be implemented inside the builder
        public void Test_AddForeignKey_ToExistingTable()
        {
            //---------------Set up test pack-------------------
            DatabaseScriptCreator databaseScriptCreator = CreateDatabaseScriptCreator();
            DBTable referencedDBTable;
            DBTable dbTable = CreateDBTableWithForeignKey(out referencedDBTable);

            //---------------Assert Precondition----------------
            Assert.AreEqual(1, dbTable.ForeignKeys.Count);
            DBForeignKey dbForeignKey = dbTable.ForeignKeys[0];
            Assert.AreSame(dbTable, dbForeignKey.ParentTable);
            Assert.AreEqual(1, dbForeignKey.ForeignKeyColumns.Count);
            DBForeignKeyColumn dbForeignKeyColumn = dbForeignKey.ForeignKeyColumns[0];
            DBColumn referencedPkDBColumn = dbForeignKeyColumn.RelatedColumn;
            DBColumn fkDBColumn = dbForeignKeyColumn.ParentColumn;
            Assert.AreSame(dbTable, dbForeignKey.ParentTable);
            //---------------Execute Test ----------------------
            string sql = databaseScriptCreator.CreateForeignKey(dbForeignKey);
            //---------------Test Result -----------------------
            string expected = string.Format
                ("ALTER TABLE `{0}` ADD CONSTRAINT `{1}` FOREIGN KEY `{1}` (`{2}`)REFERENCES `{3}` (`{4}`)",
                 dbTable.TableNameDB, dbForeignKey.ForeignKeyName, fkDBColumn.ColumnName,
                 referencedDBTable.TableNameDB, referencedPkDBColumn.ColumnName);
            StringAssert.AreEqualIgnoringCase(expected, sql);
        }*/

        [Test]
        public void Test_CreateTable_WithForeignKey()
        {
            //---------------Set up test pack-------------------
            DatabaseScriptCreator databaseScriptCreator = CreateDatabaseScriptCreator();
            
            DBTable referencedDBTable;
            DBTable dbTable = CreateDBTableWithForeignKey(out referencedDBTable);

            //---------------Assert Precondition----------------
            Assert.AreEqual(1, dbTable.ForeignKeys.Count);
            DBForeignKey dbForeignKey = dbTable.ForeignKeys[0];
            Assert.AreSame(dbTable, dbForeignKey.ParentTable);
            Assert.AreSame(referencedDBTable, dbForeignKey.RelatedTable);
            Assert.AreEqual(1, dbForeignKey.ForeignKeyColumns.Count);
            DBForeignKeyColumn dbForeignKeyColumn = dbForeignKey.ForeignKeyColumns[0];
            DBColumn referencedPkDBColumn = dbForeignKeyColumn.RelatedColumn;
            DBColumn fkDBColumn = dbForeignKeyColumn.ParentColumn;
            //---------------Execute Test ----------------------
            string sql = databaseScriptCreator.CreateTable(dbTable, new CreateTableSqlBuilder_MySql(dbTable.TableName));
            //---------------Test Result -----------------------

            string expectedCreateTable = string.Format("CREATE TABLE `{0}` (", dbTable.TableName);
            string expectedForeignKey = string.Format
                ("CONSTRAINT `{0}` FOREIGN KEY `{0}` (`{1}`) REFERENCES `{2}` (`{3}`)", dbForeignKey.ForeignKeyName,
                 fkDBColumn.ColumnName, referencedDBTable.TableName, referencedPkDBColumn.ColumnName);
            Assert.IsTrue (sql.EndsWith(", " + expectedForeignKey + ")"),
                 sql + " should end with " + ", " + expectedForeignKey + ")");
            Assert.IsTrue(sql.StartsWith(expectedCreateTable), sql + " should start with " + expectedCreateTable);
        }

        [Test]
        public void Test_CreateTable_WithAlternateKey()
        {
            //---------------Set up test pack-------------------
            DatabaseScriptCreator databaseScriptCreator = CreateDatabaseScriptCreator();
            const string alternateKeyString = "AK1";
            const string columnName = "Some Column";
            const string tableName = "testTableName";
            DBTable dbTable = CreateTableInfoWithSimpleAlternateKey(tableName, alternateKeyString, columnName);

            //---------------Assert Precondition----------------
            Assert.AreEqual(1, dbTable.Keys.Count);
            DBKey alternateDBKey = dbTable.Keys[0];
            Assert.AreEqual(1, alternateDBKey.KeyColumns.Count);
            Assert.AreEqual(tableName, dbTable.TableName);
            //---------------Execute Test ----------------------
            string sql = databaseScriptCreator.CreateTable(dbTable, new CreateTableSqlBuilder_MySql(dbTable.TableName));
            //---------------Test Result -----------------------

            string expectedCreateTable = expectedCreateTableBegin(dbTable.TableName);

            string expectedAlternateKey = string.Format(", CONSTRAINT `{0}` UNIQUE (`{1}`))", alternateKeyString, columnName);
            Assert.IsTrue
                (sql.EndsWith(expectedAlternateKey),
                 sql + " should end with " + ", " + expectedAlternateKey + ")");
            Assert.IsTrue(sql.StartsWith(expectedCreateTable), sql + " should start with " + expectedCreateTable);
        }

        [Test]
        public void Test_AddAlternateKeyToCreateTableSQLBuilder()
        {
            //---------------Set up test pack-------------------
            DatabaseScriptCreatorMySQL_Stub databaseScriptCreator = new DatabaseScriptCreatorMySQL_Stub();
            const string alternateKeyString = "AK1";
            const string columnName = "Some Column";
            const string tableName = "testTableName";
            DBTable dbTable = CreateTableInfoWithSimpleAlternateKey(tableName, alternateKeyString, columnName);
            CreateTableSqlBuilder_MySql createTableSqlBuilderMySql = new CreateTableSqlBuilder_MySql(dbTable.TableName);
            //---------------Assert Precondition----------------
            Assert.AreEqual(1, dbTable.Keys.Count);
            DBKey alternateDBKey = dbTable.Keys[0];
            Assert.AreEqual(1, alternateDBKey.KeyColumns.Count);
            Assert.AreEqual(tableName, dbTable.TableName);
            //---------------Execute Test ----------------------
            databaseScriptCreator.AddAlternateKeysToCreateTableSQLBuilder(dbTable, createTableSqlBuilderMySql);
            string sql = createTableSqlBuilderMySql.GetSql();
            //---------------Test Result -----------------------
            string expectedCreateTable = string.Format("CREATE TABLE `{0}` (", dbTable.TableName);
            string expectedAlternateKey = string.Format("CONSTRAINT `{0}` UNIQUE (`{1}`)", alternateKeyString, columnName);
            Assert.IsTrue
                (sql.EndsWith(", " + expectedAlternateKey + ")"),
                 sql + " should end with " + ", " + expectedAlternateKey + ")");
            Assert.IsTrue(sql.StartsWith(expectedCreateTable), sql + " should start with " + expectedCreateTable);
        }

        [Test]
        public void Test_AddPrimaryKeyToCreateTableSQLBuilder_PrimaryKeyNull()
        {
            //---------------Set up test pack-------------------
            DatabaseScriptCreatorMySQL_Stub databaseScriptCreator = new DatabaseScriptCreatorMySQL_Stub();
            const string alternateKeyString = "AK1";
            const string columnName = "Some Column";
            const string tableName = "testTableName";
            DBTable dbTable = CreateTableInfoWithSimpleAlternateKey(tableName, alternateKeyString, columnName);
            CreateTableSqlBuilder_MySql createTableSqlBuilderMySql = new CreateTableSqlBuilder_MySql(dbTable.TableName);
            dbTable.PrimaryKeyDB = null;
            //---------------Assert Precondition----------------
            Assert.AreEqual(1, dbTable.Keys.Count);
            DBKey alternateDBKey = dbTable.Keys[0];
            Assert.AreEqual(1, alternateDBKey.KeyColumns.Count);
            Assert.AreEqual(tableName, dbTable.TableName);
            Assert.IsNull(dbTable.PrimaryKeyDB);
            //---------------Execute Test ----------------------
            databaseScriptCreator.AddPrimaryKeyToCreateTableSQLBuilder(dbTable, createTableSqlBuilderMySql);
            string sql = createTableSqlBuilderMySql.GetSql();
            //---------------Test Result ----------------------- 
            Assert.IsFalse(sql.Contains("PRIMARY KEY"));
        }


        [Test]
        public void Test_AddForeignKeysToCreateTableSqlBuilder()
        {
            //---------------Set up test pack-------------------
            DatabaseScriptCreatorMySQL_Stub databaseScriptCreator = new DatabaseScriptCreatorMySQL_Stub();
            DBTable referencedDBTable;
            DBTable dbTable = CreateDBTableWithForeignKey(out referencedDBTable);
            CreateTableSqlBuilder_MySql createTableSqlBuilderMySql = new CreateTableSqlBuilder_MySql(dbTable.TableName);

            //---------------Assert Precondition----------------
            Assert.AreEqual(1, dbTable.ForeignKeys.Count);
            DBForeignKey dbForeignKey = dbTable.ForeignKeys[0];
            Assert.AreSame(dbTable, dbForeignKey.ParentTable);
            Assert.AreEqual(1, dbForeignKey.ForeignKeyColumns.Count);


            //---------------Execute Test ----------------------
            databaseScriptCreator.AddForeignKeysToCreateTableSqlBuilder(dbTable, createTableSqlBuilderMySql);
            string sql = createTableSqlBuilderMySql.GetSql();
            //---------------Test Result -----------------------
            DBForeignKeyColumn dbForeignKeyColumn = dbForeignKey.ForeignKeyColumns[0];
            DBColumn referencedPkDBColumn = dbForeignKeyColumn.RelatedColumn;
            DBColumn fkDBColumn = dbForeignKeyColumn.ParentColumn;

            string expectedCreateTable = string.Format("CREATE TABLE `{0}` (", dbTable.TableName);
            string expectedForeignKey = string.Format
                ("FOREIGN KEY `{0}` (`{1}`) REFERENCES `{2}` (`{3}`)", dbForeignKey.ForeignKeyName, fkDBColumn.ColumnName,
                 referencedDBTable.TableName, referencedPkDBColumn.ColumnName);
            Assert.IsTrue(sql.EndsWith(expectedForeignKey + ")"), sql + " should end with " + expectedForeignKey);
            Assert.IsTrue(sql.StartsWith(expectedCreateTable), sql + " should start with " + expectedCreateTable);
        }


        public class DatabaseScriptCreatorMySQL_Stub : DatabaseScriptCreator
        {
            public CreateTableSqlBuilder AddForeignKeysToCreateTableSqlBuilder
                (DBTable dbTable, CreateTableSqlBuilder_MySql createTableSqlBuilderMySql)
            {
                return DatabaseScriptCreator.AddForeignKeysToCreateTableSqlBuilder(dbTable, createTableSqlBuilderMySql);
            }

            public CreateTableSqlBuilder AddAlternateKeysToCreateTableSQLBuilder
                (DBTable dbTable, CreateTableSqlBuilder_MySql createTableSqlBuilderMySql)
            {
                return DatabaseScriptCreator.AddAlternateKeysToCreateTableSQLBuilder
                    (dbTable, createTableSqlBuilderMySql);
            }

            public CreateTableSqlBuilder AddPrimaryKeyToCreateTableSQLBuilder
                (DBTable dbTable, CreateTableSqlBuilder_MySql createTableSqlBuilderMySql)
            {
                return DatabaseScriptCreator.AddPrimaryKeyToCreateTableSQLBuilder(dbTable, createTableSqlBuilderMySql);
            }
        }

//        public class TestDatabaseScriptCreator_MySQL : TestDatabaseScriptCreator
//        {
//            // ReSharper disable AccessToStaticMemberViaDerivedType
//
//
//        }
    }
}